package com.roadmap.biz.common;

/* 此类包含了业务逻辑所需的数据库查询语句 */
public interface RdcQueryConst {

	// SQL
	String SQL_NEW_REQUESTS_WITH_NAME = "select {r.*}, a.realName from request as r, aspirant as a where r.recipientId=:uid and r.status=:state and r.dispatcherId=a.aspirantId order by createdTime DESC";

	String SQL_FRIENDS_WITH_NAME_BY_GROUP = "select {f.*}, a.realName from (select friend.* from friendship as friend where (friend.friendAId=:uid and friend.friendBId not in(:groups)) or (friend.friendBId=:uid and friend.friendAId not in(:groups))) as friend, aspirant as a where f.friendAId=a.aspirantId or f.friendBId=a.aspirantId order by f.frequency DESC";
	
	String SQL_FRIENDS_ORDERBY_NAME = "select {f.*}, a.realName from friendship as f, aspirant as a where (f.friendAId=:uid and f.friendBId=a.aspirantId) or (f.friendBId=:uid and f.friendAId=a.aspirantId) order by f.frequency DESC,a.realName ASC";
	
	String SQL_COMMENTS_ORDERBY_TIME = "select {c.*}, a.realName from comment as c, aspirant as a where typeId=:tid and c.authorId=a.aspirantId order by updatedTime ASC";
	
	String SQL_PLAN_STATISTICS = "select count(t.taskId) as amount, sum(t.proportionOfPlan) as assigned, sum(t.proportionOfPlan * (select sum(e.proportionOfTask) from Execution as e where e.taskId=t.taskId group by e.taskId)) as progress from LifeTask as t where t.planId=:pid and t.status<>:state";
	
	String SQL_PLAN_WITH_GOAL = "select {plan.*}, goal.name, goal.status from lifeplan as plan, lifegoal as goal where goal.aspirantId=? and plan.planId=? and plan.goalId=goal.goalId and goal.status<>?";
	
	String SQL_PLANS_WITH_GOALS = "select {plan.*},goal.name,goal.status from lifeplan as plan, lifegoal as goal where goal.aspirantId=? and (goal.status=? || goal.status=? )and plan.goalId=goal.goalId order by plan.createdTime DESC";
	
	String SQL_PLANS_STATISTICS = "select t.planId as pid, count(t.taskId) as amount, sum(t.proportionOfPlan * (select sum(e.proportionOfTask) from Execution as e where e.taskId=t.taskId group by e.taskId)) as progress from LifeTask as t where t.planId in(:planIDs) and (t.status<>:state1 || t.status<>:state2)group by t.planId";
	
	String SQL_PLANS_BY_USER = "select {p.*} from lifeplan as p, lifegoal as g where p.aspirantId=? and p.goalId=g.goalId and g.status<>?";
	
	String SQL_UPDATE_GOAL_STATUS = "update LifeGoal set status=:state, updatedTime=:dt where id=:gid and status=:state2";
	
	String SQL_UPDATE_TASKS_STATUS = "update LifeTask set status=:state, updatedTime=:dt where planId=:pid";
	
	String SQL_GOALS_BY_STATUES = "select {g.*} from lifegoal as g where g.status in (:states) and g.aspirantId=:owner";
	
	String SQL_GOAL_STATISTICS = "select count(t.taskId) as amount, sum(t.proportionOfGoal) as assigned, sum(t.proportionOfGoal * (select sum(e.proportionOfTask) from Execution as e where e.taskId=t.taskId group by e.taskId)) as progress from LifeTask as t where t.goalId=:gid and t.status<>:state";
	
	String SQL_GOALS_STATISTICS = "select t.goalId as gid, count(t.taskId) as amount, sum(t.proportionOfGoal) as assigned, sum(t.proportionOfGoal * (select sum(e.proportionOfTask) as progress from Execution as e where e.taskId=t.taskId group by e.taskId)) as progress from LifeTask as t where t.goalId in(:goalIDs) and t.status<>:state group by t.goalId";
	
	String SQL_GOAL_PLAN_BY_TASK = "select {g.*}, {p.*} from lifegoal as g, lifeplan as p where p.planId=:pid and p.goalId=g.goalId and p.aspirantId=:uid";
	
	String SQL_TASKS_WITH_GOAL = "select {task.*}, goal.name from (select t.* from lifetask as t where t.taskId=:tid and t.status<>:state and t.aspirantId=:uid) as task left join lifegoal as goal on task.goalId=goal.goalId";
	
	String SQL_TASK_STATISTICS = "select count(id), sum(executeTimeLength), sum(proportionOfTask), max(createdTime) from Execution where taskId=?";
	
	String SQL_TASK_WITH_GOAL_BY_STATUS = "select {task.*}, goal.name from (select t.* from lifetask as t where t.aspirantId=? and (t.status=? or t.status=?)) as task left join lifegoal as goal on task.goalId=goal.goalId order by task.createdTime DESC";
	
	String SQL_TASKS_STATISTICS = "select tx.taskId as tid, ex.amount, ex.totalTime, ex.progress, ex.created from (select t.taskId from lifetask as t where t.taskId in (:taskIDs)) as tx left join (select e.taskId, count(e.taskId) as amount, sum(e.executeTimeLength) as totalTime, sum(e.proportionOfTask) as progress, max(e.createdTime) as created from execution as e where e.taskId in (:taskIDs) group by e.taskId) as ex on tx.taskId=ex.taskId";
	
	String SQL_SENT_MSGS_USER_BY_USER = "select {m.*}, m.aspirantId, m.realName from (select msg.*, d.aspirantId, d.realName from message as msg, (select dlg.dialogId, a.aspirantId, a.realName from dialog as dlg, aspirant as a where (dlg.sponsorId=:me and dlg.participatorId=a.aspirantId) || (dlg.participatorId=:me and dlg.sponsorId=a.aspirantId)) as d where msg.dialogId=d.dialogId and msg.dispatcherId=:me order by msg.createdTime DESC) as m group by m.dialogId having m.createdTime<=max(m.createdTime) order by m.createdTime DESC";
	
	String SQL_RECEIEVED_MSGS_USER_BY_USER = "select {m.*}, m.aspirantId, m.realName from (select msg.*, d.aspirantId, d.realName from message as msg, (select dlg.dialogId, a.aspirantId, a.realName from dialog as dlg, aspirant as a where (dlg.sponsorId=:me and dlg.participatorId=a.aspirantId) || (dlg.participatorId=:me and dlg.sponsorId=a.aspirantId)) as d where msg.dialogId=d.dialogId and msg.dispatcherId=d.aspirantId order by msg.createdTime DESC) as m group by m.dialogId order by m.createdTime DESC";
	
	String SQL_MSGS_BY_DIALOG = "select {m.*}, a.realName from message as m, aspirant as a where m.dialogId=:dlgid and m.dispatcherId=a.aspirantId order by m.createdTime ASC";
	
	String SQL_USERS_BY_DIALOG = "select {d.*}, a.aspirantId, a.realName from dialog as d, aspirant as a where d.dialogId=:dlgid and (d.sponsorId=a.aspirantId || d.participatorId=a.aspirantId)";
	
	String SQL_GOAL_PLAN_BY_ID = "select {g.*}, {p.*} from (select * from lifegoal where goalId=:gid and aspirantId=:uid and status<>:status) as g left join lifeplan as p on g.goalId=p.goalId";
	
	String SQL_GOAL_PLAN_IDS_BY_GOAL = "select p.planId, g.goalId from (select goalId from lifegoal where goalId=:gid and aspirantId=:uid and status<>:status) as g left join lifeplan as p on g.goalId=p.goalId";

	String SQL_GOAL_EXT_BY_ID = "select {g.*}, g.ownerName, p.planId, p.name from (select gl.*, a.realName as ownerName from lifegoal as gl, aspirant as a where gl.goalId=:gid and gl.status<>:status and gl.aspirantId=:uid and gl.aspirantId=a.aspirantId) as g left join lifeplan as p on g.goalId=p.goalId";
	
	String SQL_GOAL_BY_USER = "from LifeGoal where aspirantId=? and status<>?";
	
	String SQL_COMMENT_COUNT_BY_TYPEID = "select typeId, count(typeId) as amount from comment group by typeId having typeId in (:ids)";
	
	// HQL
	String HQL_REQUEST_BETWEEN_TWO_USERS = "from Request where (id=? and recipientId=?) or (id=? and recipientId=?)";

	String HQL_FRIENDSHIP_BETWEEN_TWO_USERS = "from Friendship where (id=? and friendId=?) or (id=? and friendId=?)";
	
	String HQL_DELETE_LIMITEDACCESS_BY_ID = "delete from LimitedAccess where id=?";
	
	String HQL_LIMITEDACCESSES_BY_IDS = "from LimitedAccess where id in (:ids)";
	
	String HQL_ALL_GROUPS_BY_USER = "from Category where ownerId=? and entryType=?";
	
	String HQL_DELETE_FRIENDSHIP_BETWEEN_TWO_USERS = "delete from Friendship where (friendAId=:aid and friendBId=:bid) or (friendBId=:aid and friendAId=:bid)";

	String HQL_GROUP_BY_USER = "from Category where ownerId=? and memberIDs like ?";
	
	String HQL_GOAL_BY_USER_ID = "from LifeGoal where id=? and aspirantId=? and status<>?";
	
	String HQL_UPDATE_GOAL_STATUS = "update LifeGoal set status=:state, updatedTime=:dt where id=:gid";
	
	String HQL_UPDATE_TASK_STATUS = "update LifeTask set status=:state, updatedTime=:dt where goalId=:gid";
	
	String HQL_USERS_BY_NAME = "from Aspirant where realName=? and aspirantId<>?";
	
	String HQL_USERS_BY_NAME_SEX = "from Aspirant where realName=? and sex=? and aspirantId<>?";
	
	String HQL_USER_BY_MAIL = "from Aspirant where mail=? and aspirantId<>?";
	
	String HQL_TASK_BY_ID = "from LifeTask where id=? and aspirantId=? and status<>?";
	
	String HQL_EXEC_BY_TASK = "from Execution where taskId=? order by executeDate DESC, updatedTime DESC";
	
	String HQL_GROUP_BY_ID = "from Category where id=? and ownerId=?";
	
	String HQL_DELETE_PLAN = "delete from LifePlan where planId=:pid";
	
	String HQL_TASK_EXEC_BY_ID = "from LifeTask t, Execution e where e.id=? and e.taskId=t.id and t.aspirantId=? and t.status<>?";
	
	String HQL_TASKS_BY_GOAL = "from LifeTask where goalId=? and aspirantId=? and status<>? order by createdTime DESC";
	
	String HQL_TASKS_BY_USER = "from LifeTask where aspirantId=? and status<>? order by createdTime DESC";
	
	String HQL_EXECS_BY_TASK = "from Execution where taskId=? order by executeDate DESC, updatedTime DESC";
	
}
